using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;

namespace WPFExcelReport
{
    public partial class VAMEINST
    {
        public void SetUpNewRow(ref DataRow dwReturn)
        {
            #region
            #endregion
            switch (app.company)
            {
                case "YGR":
                case "SXN":
                case "TST":
                case "UBT":
                default:
                    #region
                    dwReturn["AMEMANDANT"] = "00";
                    dwReturn["AMENRKRS"] = "  ";
                    dwReturn["AMELFDNR"] = "0200";
                    dwReturn["AMEPOS"] = "01";
                    dwReturn["AMEMODNR"] = "010";
                    dwReturn["AMEEINSTNR"] = "010";
                    dwReturn["AMEBEZ"] = "DX                                      ";
                    dwReturn["AMEKBEZ"] = "D         ";
                    dwReturn["AMEPROGKZ"] = " ";
                    dwReturn["AMEEAKZ"] = "N";
                    dwReturn["AMEWERKKZ"] = "N";
                    dwReturn["AMEPFLKZ"] = "N";
                    dwReturn["AMERICHTKZ"] = "N";
                    dwReturn["AMEMASS"] = "                    ";
                    dwReturn["AMEMESSGR"] = "                    ";
                    dwReturn["AMEFDLG"] = "05";
                    dwReturn["AMEFDNUM"] = "A";
                    dwReturn["AMEFDNKST"] = "0";
                    dwReturn["AMEDRUCKKZ"] = "J";
                    dwReturn["AMERUEKZ"] = "N";
                    dwReturn["AMEBDEKEN"] = "        ";
                    dwReturn["AMEMESCHL"] = "00";
                    dwReturn["AMEWERTNR"] = "0";
                    dwReturn["AMEWERTVON"] = "A               ";
                    dwReturn["AMEWERTVONN"] = "0000000000000000";
                    dwReturn["AMEWERTBIS"] = "Z               ";
                    dwReturn["AMEWERTBISN"] = "0000000000000000";
                    dwReturn["AMEWERTEX1"] = "                ";
                    dwReturn["AMEWERTEX2"] = "                ";
                    dwReturn["AMEWERTEX3"] = "                ";
                    dwReturn["AMEWERTEX4"] = "                ";
                    dwReturn["AMEWERTEXN1"] = "0000000000000000";
                    dwReturn["AMEWERTEXN2"] = "0000000000000000";
                    dwReturn["AMEWERTEXN3"] = "0000000000000000";
                    dwReturn["AMEWERTEXN4"] = "0000000000000000";
                    dwReturn["AMESCHWWERT"] = "0";
                    dwReturn["AMESCHWWERTP"] = " ";
                    dwReturn["AMETOLMIN"] = "0";
                    dwReturn["AMETOLPLU"] = "0";
                    dwReturn["AMETOLP"] = " ";
                    dwReturn["AMELISTWERT"] = "0";
                    dwReturn["AMELEITWEG"] = "          ";
                    dwReturn["AMESTOPPEN"] = "  ";
                    dwReturn["AMEINTERVAL"] = "0";
                    dwReturn["AMELAUFKRS"] = "  ";
                    dwReturn["AMELAUFLFD"] = "000000";
                    dwReturn["AMELAUFPOS"] = "000";
                    dwReturn["AMEEINLEGKZ"] = "N";
                    dwReturn["AMESTSA"] = "000";
                    dwReturn["AMEEINPOS"] = "00";
                    dwReturn["AMEAUFM"] = "  ";
                    dwReturn["AMEKOSTRE"] = " ";
                    dwReturn["AMEFILLER"] = "     00                                                ";
                    dwReturn["AMEAENDZL"] = "1";
                    dwReturn["AMEAENDBEN"] = "INTEXE  ";
                    dwReturn["AMEAENDDAT"] = "20151006";
                    dwReturn["AMENEUBEN"] = "INTEXE  ";
                    dwReturn["AMENEUDAT"] = "20151005";
                    #endregion
                    break;
            }
        }
        public void InsertAMEINST(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            or1 = OracleDB.CreateCommand();
            switch (app.company)
            {
                case "YGR":
                case "SXN":
                case "TST":
                case "UBT":
                default:
                    or1.CommandText = "INSERT INTO AMEINST ( AMEMANDANT,AMENRKRS,AMELFDNR,AMEPOS,AMEMODNR,AMEEINSTNR,AMEBEZ,AMEKBEZ,AMEPROGKZ,AMEEAKZ,AMEWERKKZ,AMEPFLKZ,AMERICHTKZ,AMEMASS,AMEMESSGR,AMEFDLG,AMEFDNUM,AMEFDNKST,AMEDRUCKKZ,AMERUEKZ,AMEBDEKEN,AMEMESCHL,AMEWERTNR,AMEWERTVON,AMEWERTVONN,AMEWERTBIS,AMEWERTBISN,AMEWERTEX1,AMEWERTEX2,AMEWERTEX3,AMEWERTEX4,AMEWERTEXN1,AMEWERTEXN2,AMEWERTEXN3,AMEWERTEXN4,AMESCHWWERT,AMESCHWWERTP,AMETOLMIN,AMETOLPLU,AMETOLP,AMELISTWERT,AMELEITWEG,AMESTOPPEN,AMEINTERVAL,AMELAUFKRS,AMELAUFLFD,AMELAUFPOS,AMEEINLEGKZ,AMESTSA,AMEEINPOS,AMEAUFM,AMEKOSTRE,AMEFILLER,AMEAENDZL,AMEAENDBEN,AMEAENDDAT,AMENEUBEN,AMENEUDAT)"
                   + " VALUES (:AMEMANDANT:,:AMENRKRS:,:AMELFDNR:,:AMEPOS:,:AMEMODNR:,:AMEEINSTNR:,:AMEBEZ:,:AMEKBEZ:,:AMEPROGKZ:,:AMEEAKZ:,:AMEWERKKZ:,:AMEPFLKZ:,:AMERICHTKZ:,:AMEMASS:,:AMEMESSGR:,:AMEFDLG:,:AMEFDNUM:,:AMEFDNKST:,:AMEDRUCKKZ:,:AMERUEKZ:,:AMEBDEKEN:,:AMEMESCHL:,:AMEWERTNR:,:AMEWERTVON:,:AMEWERTVONN:,:AMEWERTBIS:,:AMEWERTBISN:,:AMEWERTEX1:,:AMEWERTEX2:,:AMEWERTEX3:,:AMEWERTEX4:,:AMEWERTEXN1:,:AMEWERTEXN2:,:AMEWERTEXN3:,:AMEWERTEXN4:,:AMESCHWWERT:,:AMESCHWWERTP:,:AMETOLMIN:,:AMETOLPLU:,:AMETOLP:,:AMELISTWERT:,:AMELEITWEG:,:AMESTOPPEN:,:AMEINTERVAL:,:AMELAUFKRS:,:AMELAUFLFD:,:AMELAUFPOS:,:AMEEINLEGKZ:,:AMESTSA:,:AMEEINPOS:,:AMEAUFM:,:AMEKOSTRE:,:AMEFILLER:,:AMEAENDZL:,:AMEAENDBEN:,:AMEAENDDAT:,:AMENEUBEN:,:AMENEUDAT:)";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
            }
            Connect2Proxy(or1);
        }
        public DataSet QAME01(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT M.* FROM  AMEINST M"
             + " WHERE AMEMANDANT=:AMEMANDANT: AND AMENRKRS=:AMENRKRS: AND AMELFDNR=:AMELFDNR: AND AMEPOS=:AMEPOS: AND AMEMODNR=:AMEMODNR: AND AMEEINSTNR=:AMEEINSTNR: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QAME02(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT AMEEINSTNR AS KEY, AMEKBEZ AS DES,M.* FROM  AMEINST M"
             + " WHERE AMEMANDANT=:AMEMANDANT: " 
             + dwData["AMENRKRS"].ToString()
            + dwData["AMELFDNR"].ToString()
            + dwData["AMEPOS"].ToString()
            + dwData["AMEMODNR"].ToString()
            + dwData["AMEEINSTNR"].ToString();
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QAME03(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT DISTINCT AMEBDEKEN,AMEBDEKEN AS KEY, AMEBEZ AS DES,AMBEZ FROM  AMEINST M JOIN AMSTAMM P ON AMEMANDANT=AMMANDANT AND AMENRKRS=AMNRKRS AND AMELFDNR=AMLFDNR  AND AMEPOS=AMPOS"
             + " WHERE AMEMANDANT=:AMEMANDANT: "
             + dwData["AMEBEZ"].ToString();
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QAME04(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT X.* FROM  AMEINST JOIN AMSTAMM P ON AMEMANDANT=P.AMMANDANT AND AMENRKRS=P.AMNRKRS AND  AMELFDNR=P.AMLFDNR  AND  AMEPOS=P.AMPOS "
                + " JOIN AMSTAMM X ON X.AMMANDANT=P.AMMANDANT AND X.AMART=P.AMART AND  X.AMTYP=P.AMTYP AND X.AMMANDANT||X.AMNRKRS||X.AMLFDNR||X.AMPOS<>AMEMANDANT||AMENRKRS||AMELFDNR||AMEPOS  "
                            + " WHERE AMEMANDANT=:AMEMANDANT: AND AMENRKRS=:AMENRKRS:  AND  AMELFDNR=:AMELFDNR:  AND  AMEPOS=:AMEPOS:  AND AMEMODNR=:AMEMODNR: AND AMEEINSTNR=:AMEEINSTNR:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData).Replace("''", "");
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QAME05(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT DISTINCT SUBSTR(AMEBDEKEN,1,6) AS AMEBDEKEN,SUBSTR(AMEBDEKEN,1,6) AS KEY FROM  AMEINST M "
                + " JOIN AMSTAMM P ON AMEMANDANT=AMMANDANT AND AMENRKRS=AMNRKRS AND AMELFDNR=AMLFDNR  AND AMEPOS=AMPOS"
             + " WHERE AMEMANDANT=:AMEMANDANT: "
             + dwData["AMEBEZ"].ToString()
             + " ORDER BY 1";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QAME06(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT M.* FROM  AMEINST M JOIN AMSTAMM P ON AMEMANDANT=AMMANDANT AND AMENRKRS=AMNRKRS AND AMELFDNR=AMLFDNR  AND AMEPOS=AMPOS"
             + " WHERE AMEMANDANT=:AMEMANDANT: "
             + dwData["AMEBDEKEN"].ToString()
            + dwData["AMEBEZ"].ToString();
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QAME07(string machineid)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT AMEMANDANT||AMENRKRS||AMELFDNR||AMEPOS||AMEMODNR||AMEEINSTNR AS BDEKEY,AMEBDEKEN AS BDEKEN,AMEBEZ,AMEBEZ AS DES,AMEWERTNR FROM "
                + " AMEINST E JOIN AMSTAMM M ON M.AMMANDANT = AMEMANDANT AND M.AMNRKRS = AMENRKRS AND AMLFDNR = AMELFDNR AND M.AMPOS = AMEPOS"
                + " WHERE E.AMEMANDANT = '00' AND AMENRKRS||AMELFDNR||AMEPOS='" + machineid.Substring(0,8) + "' ORDER BY 1";
            SResult = Connect2Proxy(or1);
            return SResult;
        }
        public void DAME00(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM  AMEINST"
                + " WHERE AMEMANDANT=:AMEMANDANT:  ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);

        }
        public void DAME01(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM  AMEINST "
             + " WHERE AMEMANDANT=:AMEMANDANT: AND AMENRKRS=:AMENRKRS: AND AMELFDNR=:AMELFDNR: AND AMEPOS=:AMEPOS: AND AMEMODNR=:AMEMODNR: AND AMEEINSTNR=:AMEEINSTNR: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);

        }
        public void DAME02(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM  AMEINST"
             + " WHERE AMEMANDANT=:AMEMANDANT: AND AMENRKRS=:AMENRKRS: AND AMELFDNR=:AMELFDNR: AND AMEPOS=:AMEPOS:   ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);

        }
        public void DAME03(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM  AMEINST  "
                            + " WHERE AMEMANDANT=:AMEMANDANT: AND AMENRKRS=:AMENRKRS:  AND  AMELFDNR=:AMELFDNR:  AND  AMEPOS=:AMEPOS: AND AMEMODNR=:AMEMODNR:  ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);

        }

        public void UpdateAMEINST(DataRow dwData, int option = 1)
        {
            if (option == 0)
            {
                switch (app.company)
                {
                    case "YGR":
                    case "SXN":
                    case "TST":
                    case "UBT":
                    default:
                        UAME01(dwData);
                        break;
                }
            }

        }
        public void UAME01(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "UPDATE AMEINST SET AMEMANDANT=:AMEMANDANT:,AMENRKRS=:AMENRKRS:,AMELFDNR=:AMELFDNR:,AMEPOS=:AMEPOS:,AMEMODNR=:AMEMODNR:,AMEEINSTNR=:AMEEINSTNR:,AMEBEZ=:AMEBEZ:,AMEKBEZ=:AMEKBEZ:,AMEPROGKZ=:AMEPROGKZ:,AMEEAKZ=:AMEEAKZ:,AMEWERKKZ=:AMEWERKKZ:,AMEPFLKZ=:AMEPFLKZ:,AMERICHTKZ=:AMERICHTKZ:,AMEMASS=:AMEMASS:,AMEMESSGR=:AMEMESSGR:,AMEFDLG=:AMEFDLG:,AMEFDNUM=:AMEFDNUM:,AMEFDNKST=:AMEFDNKST:,AMEDRUCKKZ=:AMEDRUCKKZ:,AMERUEKZ=:AMERUEKZ:,AMEBDEKEN=:AMEBDEKEN:,AMEMESCHL=:AMEMESCHL:,AMEWERTNR=:AMEWERTNR:,AMEWERTVON=:AMEWERTVON:,AMEWERTVONN=:AMEWERTVONN:,AMEWERTBIS=:AMEWERTBIS:,AMEWERTBISN=:AMEWERTBISN:,AMEWERTEX1=:AMEWERTEX1:,AMEWERTEX2=:AMEWERTEX2:,AMEWERTEX3=:AMEWERTEX3:,AMEWERTEX4=:AMEWERTEX4:,AMEWERTEXN1=:AMEWERTEXN1:,AMEWERTEXN2=:AMEWERTEXN2:,AMEWERTEXN3=:AMEWERTEXN3:,AMEWERTEXN4=:AMEWERTEXN4:,AMESCHWWERT=:AMESCHWWERT:,AMESCHWWERTP=:AMESCHWWERTP:,AMETOLMIN=:AMETOLMIN:,AMETOLPLU=:AMETOLPLU:,AMETOLP=:AMETOLP:,AMELISTWERT=:AMELISTWERT:,AMELEITWEG=:AMELEITWEG:,AMESTOPPEN=:AMESTOPPEN:,AMEINTERVAL=:AMEINTERVAL:,AMELAUFKRS=:AMELAUFKRS:,AMELAUFLFD=:AMELAUFLFD:,AMELAUFPOS=:AMELAUFPOS:,AMEEINLEGKZ=:AMEEINLEGKZ:,AMESTSA=:AMESTSA:,AMEEINPOS=:AMEEINPOS:,AMEAUFM=:AMEAUFM:,AMEKOSTRE=:AMEKOSTRE:,AMEFILLER=:AMEFILLER:,AMEAENDZL=:AMEAENDZL:,AMEAENDBEN=:AMEAENDBEN:,AMEAENDDAT=:AMEAENDDAT:,AMENEUBEN=:AMENEUBEN:,AMENEUDAT=:AMENEUDAT:"
             + " WHERE AMEMANDANT=:AMEMANDANT: AND AMENRKRS=:AMENRKRS: AND AMELFDNR=:AMELFDNR: AND AMEPOS=:AMEPOS: AND AMEMODNR=:AMEMODNR: AND AMEEINSTNR=:AMEEINSTNR: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
    }
}
